﻿using System;
using System.Data;
using System.Text;
using System.Data.OleDb;
using Maticsoft.DBUtility;//Please add references
namespace Maticsoft
{
    /// <summary>
    /// 类USERS。
    /// </summary>
    [Serializable]
    public partial class USERS
    {
        #region Model
        private int _id;
        private string _login;
        private string _name;
        private string _pwd;
        private string _email;
        private string _tel;
        private DateTime _cdate = DateTime.Now;
        /// <summary>
        /// 
        /// </summary>
        public int id
        {
            set { _id = value; }
            get { return _id; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string login
        {
            set { _login = value; }
            get { return _login; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string name
        {
            set { _name = value; }
            get { return _name; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string pwd
        {
            set { _pwd = value; }
            get { return _pwd; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string email
        {
            set { _email = value; }
            get { return _email; }
        }
        /// <summary>
        /// 
        /// </summary>
        public string tel
        {
            set { _tel = value; }
            get { return _tel; }
        }
        public DateTime CDATE
        {

            set { _cdate = value; }
            get { return _cdate; }
        }
        #endregion Model

        #region  Method

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(String login, String id)
        {
            OleDbDataReader dr = null;
            try
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select LOGIN from [USERS]");
                strSql.Append(" where LOGIN=@LOGIN ");
                if (id != "-1")
                {
                    strSql.Append(" and id <>" + id);
                }
                OleDbParameter[] parameters = {
					new OleDbParameter("@LOGIN", OleDbType.VarChar,255)};
                parameters[0].Value = login;
                dr = DbHelperOleDb.ExecuteReader(strSql.ToString(), parameters);
                return dr.HasRows;
            }
            catch (Exception ex) { return true; }
            finally
            {
                dr.Close();
                dr.Dispose();
            }
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add()
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into [USERS] (");
            strSql.Append("login,name,pwd,email,tel,cdate)");
            strSql.Append(" values (");
            strSql.Append("@login,@name,@pwd,@email,@tel,@cdate)");
            OleDbParameter[] parameters = {
					new OleDbParameter("@login", OleDbType.VarChar,255),
					new OleDbParameter("@name", OleDbType.VarChar,255),
					new OleDbParameter("@pwd", OleDbType.VarChar,255),
					new OleDbParameter("@email", OleDbType.VarChar,255),
                    new OleDbParameter("@tel", OleDbType.VarChar,255),
                    new OleDbParameter("@cdate", OleDbType.Date)};
            parameters[0].Value = login;
            parameters[1].Value = name;
            parameters[2].Value = pwd;
            parameters[3].Value = email;
            parameters[4].Value = tel;
            parameters[5].Value = CDATE;

            return DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters) > 0;
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update [USERS] set ");
            strSql.Append("login=@login,");
            strSql.Append("name=@name,");
            strSql.Append("email=@email,");
            strSql.Append("tel=@tel");
            strSql.Append(" where id=@id ");
            OleDbParameter[] parameters = {
					new OleDbParameter("@login", OleDbType.VarChar,255),
					new OleDbParameter("@name", OleDbType.VarChar,255),
					new OleDbParameter("@email", OleDbType.VarChar,255),
					new OleDbParameter("@tel", OleDbType.VarChar,255),
					new OleDbParameter("@id", OleDbType.Integer,4)};
            parameters[0].Value = login;
            parameters[1].Value = name;
            parameters[2].Value = email;
            parameters[3].Value = tel;
            parameters[4].Value = id;

            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from [USERS] ");
            strSql.Append(" where id=@id ");
            OleDbParameter[] parameters = {
					new OleDbParameter("@id", OleDbType.Integer,4)};
            parameters[0].Value = id;

            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,login,name,tel,email,cdate ");
            strSql.Append(" FROM [USERS] ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperOleDb.Query(strSql.ToString());
        }
        public String GetList(int page, int rows)
        {
            DataSet ds = GetList("ID>-1");
            if (ds != null && ds.Tables.Count != 0)
            {
                DataTable dt = ds.Tables[0];
                if (dt != null && dt.Rows.Count != 0)
                {
                    DataTable newDt = dt.Clone();
                    int rowCount = dt.Rows.Count - dt.Rows.Count % rows == rows * (page - 1) ? dt.Rows.Count : page * rows;
                    for (int i = rows * (page - 1); i < rowCount; i++)
                    {
                        Object[] dr = dt.Rows[i].ItemArray;
                        newDt.Rows.Add(dr);
                    }
                    return "{\"total\":" + dt.Rows.Count + ",\"rows\":" + DataTableExt.DataTableToJson(newDt).ToString() + "}";
                }
                return "{\"total\":0,\"rows\":[]}";
            }
            return "{\"total\":0,\"rows\":[]}";
        }
        #endregion  Method
    }
}

